package com.qf.index;//package com.qf.index;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.concurrent.Executors;

public class Test4 {

    public static void main(String[] args) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        System.out.println(format.format(new Date()) + " 程序开始了~~~");
        String limit = "";
//        String limit = " limit 1";
        int onePageNum = 1000;

        String tempInfoSumTable = "scholar_25_02.temp_info_sum";
        insertTable3(tempInfoSumTable, "id");

        updateTable1(tempInfoSumTable, "id", "info`,`sum");
        updateTable2(tempInfoSumTable, "id", "info`,`sum");
        updateTable3(tempInfoSumTable, "id", "info`,`sum");

        System.out.println(format.format(new Date()) + " 程序结束了~~~" + getTableNumber(tempInfoSumTable, "id"));
    }

    public static void updateTable2(String tempInfoSumTable, String autoId, String field) throws SQLException {
        // 虚拟线程+批量更新方法更新耗时: 252198
        int onePageNum = 1000;
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + tempInfoSumTable).intValue();
        Collection<Integer> tableList = new ArrayList<>();
        NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
        long t1 = System.currentTimeMillis();
        String sql = "UPDATE " + tempInfoSumTable + " SET `sum` = ? WHERE id = ?";
        try (var executor = Executors.newVirtualThreadPerTaskExecutor()) {
            for (Integer startId : tableList) {
                executor.submit(() -> {
                    try {
                        List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + tempInfoSumTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                        List<Object[]> batchParams = new ArrayList<>();
                        for (Entity entry : tableData) {
                            Integer id = entry.getInt(autoId);
                            String info = entry.getStr("info");
                            if (null != id && id > 0) {
                                Object[] params = new Object[]{null == info ? 0 : info.length(), id};
                                batchParams.add(params);
                            }
                        }
                        if (!batchParams.isEmpty()) {
                            Db.use().executeBatch(sql, batchParams);
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                });
            }
        }
        long t0 = System.currentTimeMillis();
        System.out.println("虚拟线程+批量更新方法更新耗时: " + (t0 - t1));
    }

    public static void updateTable1(String tempInfoSumTable, String autoId, String field) throws SQLException {
        // parallelStream+批量更新方法更新耗时: 271841
        int onePageNum = 1000;
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + tempInfoSumTable).intValue();
        Collection<Integer> tableList = new ArrayList<>();
        NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
        long t1 = System.currentTimeMillis();
        String sql = "UPDATE " + tempInfoSumTable + " SET `sum` = ? WHERE id = ?";
        tableList.parallelStream().forEach(startId -> {
            try {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + tempInfoSumTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                List<Object[]> batchParams = new ArrayList<>();
                for (Entity entry : tableData) {
                    Integer id = entry.getInt(autoId);
                    String info = entry.getStr("info");
                    if (null != id && id > 0) {
                        Object[] params = new Object[]{null == info ? 0 : info.length(), id};
                        batchParams.add(params);
                    }
                }
                if (!batchParams.isEmpty()) {
                    Db.use().executeBatch(sql, batchParams);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
        long t0 = System.currentTimeMillis();
        System.out.println("parallelStream+批量更新方法更新耗时: " + (t0 - t1));
    }

    public static void updateTable3(String tempInfoSumTable, String autoId, String field) throws SQLException {
        // parallelStream+普通插入方法更新耗时: 237213
        int onePageNum = 1000;
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + tempInfoSumTable).intValue();
        Collection<Integer> tableList = new ArrayList<>();
        NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
        long t1 = System.currentTimeMillis();
        tableList.parallelStream().forEach(startId -> {
            try {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + tempInfoSumTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    Integer id = entry.getInt(autoId);
                    String info = entry.getStr("info");
                    if (null != id && id > 0) {
                        try {
                            Db.use().update(Entity.create().set("sum", null == info ? 0 : info.length()), Entity.create(tempInfoSumTable).set(autoId, id));
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
        long t0 = System.currentTimeMillis();
        System.out.println("parallelStream+普通插入方法更新耗时: " + (t0 - t1));
    }

    public static void insertTable3(String tempInfoSumTable, String autoId) throws SQLException {
        List<Integer> idList = new ArrayList<>();
        for (int i = 0; i < 1000000; i++) {
            idList.add(i);
        }
        Db.use().execute("truncate table " + tempInfoSumTable);
        long t4 = System.currentTimeMillis();
        idList.parallelStream().forEach(id -> {
            insert(tempInfoSumTable, id);
        });
        long t5 = System.currentTimeMillis();
        System.out.println("--parallelStream方法耗时--" + (t5 - t4) + " 插入的数量：" + getTableNumber(tempInfoSumTable, autoId));
    }

    public static void insert(String tempInfoSumTable, int id) {
        try {
            Db.use().insert(Entity.create(tempInfoSumTable).set("info", Integer.toString(id)));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static int getTableNumber(String tempInfoSumTable, String autoId) throws SQLException {
        List<Entity> tableNumberList = Db.use().query("select count(1) as count from " + tempInfoSumTable);
        if (tableNumberList.get(0).getInt("count") > 0) {
            return Db.use().queryNumber("select max(" + autoId + ") from " + tempInfoSumTable).intValue();
        }
        return 0;
    }


}
